﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;
/// <summary>
///DataOperate 的摘要说明
/// </summary>
public class DataOperate
{
    private SqlHelper sqlHelper = null;
    private SqlConnection con = null;
    private string sqlStr = string.Empty;
    private SqlCommand cmd = null;
    private SqlDataReader reader = null;
    public DataOperate()
    {
        sqlHelper = new SqlHelper();
    }
    /// <summary>
    /// 验证用户登录
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="password">密码</param>
    /// <returns></returns>
    public bool Validate_User_Logon(string userName, string password, string companyid)
    {
        try
        {
            if (!string.IsNullOrEmpty(userName) && !string.IsNullOrEmpty(password))
            {
                sqlStr = string.Format("select count(*) from dbo.UserInfo where User_Name='{0}' and User_Pass='{1}' and company_id='{2}'", userName, password, companyid);
                con = sqlHelper.GetSqlConnection();//获取数据库连接
                int result;
                int.TryParse(sqlHelper.GetExecuteScalar(sqlStr, con).ToString(), out result);
                sqlHelper.CloseConnection(con);//关闭数据库连接
                if (result > 0)
                    return true;
                else
                    return false;
            }
            else
            {
                return false;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("类DataOperate中Validate_User_Logon出现异常:" + ex.Message, ex);
        }
    }
    /// <summary>
    /// 获取店铺列表
    /// </summary>
    /// <returns></returns>
    public string Get_Deport_List(string companyid)
    {
        try
        {
            //sqlStr = "select * from dbo.ClientInfo where ClientId like 'Z%'";
            sqlStr = string.Format("select * from dbo.ClientInfo where companyid='{0}'", companyid);
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);
            reader = cmd.ExecuteReader();
            //StringBuilder sb = new StringBuilder();
            IList<ClientInfo> client_list = new List<ClientInfo>();
            while (reader.Read())
            {
                //sb.AppendFormat("{\"ClientId\":\"{0}\",\"ClientName\":\"{1}\"}",reader.GetString(0),reader.GetString(1));
                client_list.Add(new ClientInfo()
                {
                    ClientId = reader.GetString(0),
                    ClientName = reader.GetString(1)
                });
            }

            sqlHelper.CloseConnection(con);
            //return sb.ToString();
            return JsonHelper.JsonSerializer<IList<ClientInfo>>(client_list);
        }
        catch (Exception ex)
        {

            throw new Exception("类DataOperate中Get_Deport_List出现异常:" + ex.Message, ex);
        }
    }
    /// <summary>
    /// 获取销售报表
    /// </summary>
    /// <param name="sdate">开始日期</param>
    /// <param name="edate">结束日期</param>
    /// <returns></returns>
    public string Get_Sale_Info(string sdate, string edate, string clientId, string companyid)
    {
        try
        {
            StringBuilder sb = new StringBuilder();
            string tmp = string.Empty;
            sb.AppendFormat("select s.ClientId,c.ClientName,left(MakeBillDate,7) as month,SUM(Money) as sale,isnull(b.BackSection,0) as BackSection ");
            sb.AppendFormat("from SaleInfo as s left join dbo.ClientInfo as c on s.ClientId=c.ClientId ");
            sb.AppendFormat("left join dbo.BackMoney as b on b.ClientId=s.ClientId and left(MakeBillDate,7)=b.Month ");
            if (string.IsNullOrEmpty(clientId))
            {
                sb.AppendFormat("where company_id='{0}' and left(MakeBillDate,7) >='{1}' and left(MakeBillDate,7)<='{2}' ", companyid, sdate, edate);
                tmp = sb.ToString();
            }
            else
            {
                //sb.AppendFormat("where left(MakeBillDate,7) >='{0}' and left(MakeBillDate,7)<='{1}' and  s.ClientId='{2}' ", sdate, edate, clientId);
                sb.AppendFormat("where left(MakeBillDate,7) >='{0}' and left(MakeBillDate,7)<='{1}'  and s.ClientId in(", sdate, edate);
                string[] cids = clientId.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string cid in cids)
                {
                    sb.AppendFormat("'{0}',", cid);
                }
                tmp = sb.ToString().TrimEnd(',') + ")";

            }
            sqlStr = tmp + " group by s.ClientId,c.ClientName,left(MakeBillDate,7),b.BackSection order by s.ClientId asc ";
            //sb.AppendFormat(" group by s.ClientId,c.ClientName,left(MakeBillDate,7),b.BackSection order by s.ClientId asc ");
            //sqlStr = sb.ToString();
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);
            reader = cmd.ExecuteReader();
            IList<SaleInfo> sale_list = new List<SaleInfo>();
            while (reader.Read())
            {
                sale_list.Add(new SaleInfo()
                {
                    ClientId = reader.GetString(0),
                    ClientName = reader.GetString(1),
                    Month = reader.GetString(2),
                    Sale = reader.GetDouble(3),
                    BackSection = reader.GetDouble(4)
                });
            }
            HttpContext context = HttpContext.Current;
            context.Cache["master"] = sale_list;
            return JsonHelper.JsonSerializer<IList<SaleInfo>>(sale_list);

        }
        catch (Exception ex)
        {

            throw new Exception("类DataOperate中Get_Sale_Info出现异常：" + ex.Message, ex);
        }
    }

    /// <summary>
    /// 保存回款
    /// </summary>
    /// <param name="month">日期</param>
    /// <param name="clientId">店铺号</param>
    /// <param name="money">回款金额</param>
    /// <returns></returns>
    public int Save_Back_Section(string month, string clientId, float money, string companyid)
    {
        try
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("if exists(select * from dbo.BackMoney where companyid='{0}' and ClientId='{1}' and [Month]='{2}') ", companyid, clientId, month);
            sb.AppendFormat("update dbo.BackMoney set BackSection={0} where  companyid='{1}' and ClientId='{2}' and [Month]='{3}' ", money, companyid, clientId, month);
            sb.AppendFormat("else ");
            sb.AppendFormat("insert into dbo.BackMoney(ClientId,[Month],BackSection,companyid) values('{0}','{1}',{2},'{3}') ", clientId, month, money, companyid);
            sqlStr = sb.ToString();
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);

            HttpContext context = HttpContext.Current;
            context.Cache.Remove("master");//有更新的话，这个也必须要更新

            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception("类DataOperate中Save_Back_Section出现异常：" + ex.Message, ex);
        }
        finally
        {
            sqlHelper.CloseConnection(con);
        }
    }
    /// <summary>
    /// 获取销售明细
    /// </summary>
    /// <param name="clientId">店铺id</param>
    /// <param name="month">月份</param>
    /// <returns></returns>
    public string Get_Sale_Details(string clientId, string month, string companyid)
    {
        try
        {

            sqlStr = string.Format("select MakeBillDate,Style,Number,[Money]/Number as Price,DataOrignal,BillDate,isnull(Remark,'') as Remark from dbo.SaleInfo where company_id='{0}' and ClientId='{1}' and left(MakeBillDate,7)='{2}' and Number!=0 order by MakeBillDate", companyid, clientId, month);
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);
            reader = cmd.ExecuteReader();
            IList<SaleDetail> detail_List = new List<SaleDetail>();
            while (reader.Read())
            {
                detail_List.Add(new SaleDetail()
                {
                    BillDate = reader.GetString(0).Substring(0, 10),
                    Style = reader.GetString(1),
                    Number = reader.GetDouble(2),
                    Price = reader.GetDouble(3),
                    DataOrignal = reader.GetString(4),
                    MakeBillDate = reader.GetString(5),
                    Remark = reader.GetString(6)
                });
            }
            sqlHelper.CloseConnection(con);
            HttpContext context = HttpContext.Current;
            context.Cache["details"] = detail_List;
            return JsonHelper.JsonSerializer<IList<SaleDetail>>(detail_List);
        }
        catch (Exception ex)
        {
            throw new Exception("类DataOperate中方法Get_Sale_Details出现异常：" + ex.Message);
        }
    }
}